"""
设计留言板的表
"""

import pymysql
import time
from pymysql.cursors import DictCursor

def add_message(content, person, state):
    try:
        cursor = conn.cursor()
        sql = f'insert into message_board(content, person, state, time) values ("{content}", "{person}","{state}","{time}")'
        result = cursor.execute(sql)
        conn.commit()
    except:
        conn.rollback()

def delete_message(id):
    try:
        cursor = conn.cursor()
        sql = f"delete from message_board where ID = {id}"
        cursor.execute(sql)
        conn.commit()
    except:
        conn.rollback()

def change_message(id, state):
    try:
        cursor = conn.cursor()
        sql = f'update message_board set state = "{state}" where ID = {id}'
        result = cursor.execute(sql)
        conn.commit()
    except:
        conn.rollback()

def find_message():
    cursor = conn.cursor(DictCursor)
    sql = """
        select * from message_board
    """
    ret = cursor.execute(sql)
    result = cursor.fetchone()
    print(result)


if __name__ == '__main__':
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='6666',
        database='homework'
    )
    time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    # add_message("the first added message", "Dnfer", "reviewing")
    # add_message("i love George", "Dream", "reviewing")
    # add_message("i love Dream", "George", "reviewing")
    # delete_message(1)
    # change_message(2, "pass")
    find_message()


# sqlalchemy-codegen mysql+pymysql://root:6666@127.0.0.1:3306/homework --models_layer --controller_layer --outdir dist